/****************************************************************************
*                                                                           *
* Copyright (c) Ionescu M. Marius. All rights reserved(2010).               *
*                                                                           *
****************************************************************************/

using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Data.OleDb;
using System.Data.Odbc;

namespace Hipocrate.DB
{
	/// <summary>
	/// Clasa care gestioneaza conexiunea cu baza de date.
	/// </summary>
	public class DBConnection
	{
        public static string variableConnectionString;

        public static string VariableConnectionString
        {
            get { return variableConnectionString; }
            set { variableConnectionString = value; }
        }

		public DBConnection()
		{
		}

		public static string GetConnectionString()
		{
			return ConfigurationManager.ConnectionStrings["FrameworkHipo"].ConnectionString;
		}

        private static string GetConnectionString2()
        {
            return ConfigurationManager.ConnectionStrings["Hipocrate_Old"].ConnectionString;
        }

		/// <summary>
		/// Executa o procedura cu parametri.
		/// </summary>
		/// <param name="strProcName">Numele procedurii.</param>
		/// <param name="arParamNames">Un array cu numele parametrilor (new string[]{"Nume1", "Nume2"}.</param>
		/// <param name="arParams">Un array cu valorile parametrilor (new object[]{"Valoare1", "Valoare2"}.</param>
		/// <param name="strDatabaseName">Numele bazei pe care se executa procedura.</param>
		/// <param name="nCmdType">Tipul comenzii (0 = table/view, 1 = procedura, 2 = functie).</param>
		/// <returns>DataSet</returns>
		public static DataSet GetData(string strProcName, string[] arParamNames, object[] arParams, string strDatabaseName, int nCmdType)
		{
			SqlConnection connection = null;
			DataSet ds = new DataSet();
			try
			{
				connection = new SqlConnection(GetConnectionString());
				connection.Open();
				connection.ChangeDatabase(strDatabaseName);

				SqlCommand cmd = connection.CreateCommand();
				switch (nCmdType)
				{
					case 0:
						cmd.CommandType = CommandType.Text;
						cmd.CommandText = "SELECT * FROM " + strProcName;
						break;
					case 1:
						cmd.CommandType = CommandType.StoredProcedure;
						cmd.CommandText = strProcName;
						break;
					case 2:
						cmd.CommandType = CommandType.Text;
						string[] s = new string[arParamNames.Length];
						for (int i = 0; i < s.Length; ++i)
							s[i] = "@" + arParamNames[i];
						cmd.CommandText = "SELECT * FROM " + strProcName + " (" + string.Join(", ", s) + ")";
						break;
				}
				for (int i = 0; i < arParamNames.Length; ++i)
					cmd.Parameters.AddWithValue(arParamNames[i], arParams[i]);
				SqlDataAdapter adapter = new SqlDataAdapter(cmd);
				adapter.SelectCommand.CommandTimeout = 600;

				adapter.MissingSchemaAction = MissingSchemaAction.Add;
				adapter.Fill(ds);
			}
			finally
			{
				if (connection != null)
					connection.Close();
			}

			return ds;
		}

        public static List<System.Data.SqlClient.SqlParameter> ExecuteSPWithInOutParams(string spName, string[] paramNames, object[] paramValues, params string[] outParamNames)
        {
            System.Data.SqlClient.SqlConnection connection = null;
			try
			{
				connection = new SqlConnection(GetConnectionString());
				connection.Open();

				System.Data.SqlClient.SqlCommand cmd = connection.CreateCommand();
				cmd.CommandType = CommandType.StoredProcedure;
				cmd.CommandText = spName;

				for (int i = 0; i < paramNames.Length; ++i)
					cmd.Parameters.AddWithValue(paramNames[i], paramValues[i]);

				List<System.Data.SqlClient.SqlParameter> outParams = new List<System.Data.SqlClient.SqlParameter>();

				foreach (string op in outParamNames)
				{
					System.Data.SqlClient.SqlParameter p = new System.Data.SqlClient.SqlParameter();
					p.ParameterName = op;
					p.Direction = ParameterDirection.Output;
					p.SqlDbType = SqlDbType.Decimal;
					outParams.Add(p);
					cmd.Parameters.Add(p);
				}

				cmd.CommandTimeout = 1200;
				cmd.ExecuteNonQuery();

				return outParams;
			}
			catch (SqlException ex)
			{
				string message = "Failed to execute: " + spName;
				for (int i = 0; i < paramNames.Length; ++i)
					message += (i>0?", ":" ") + paramNames[i] + " = " + (paramValues[i] is string ? "'" + paramValues[i] + "'": paramValues[i]);
				throw new ApplicationException(ex.Message, new ApplicationException(message, ex));
			}
            finally
            {
                if (connection != null)
                    connection.Close();
            }
        }

		/// <summary>
		/// Executa o procedura cu parametri.
		/// </summary>
		/// <param name="strProcName">Numele procedurii.</param>
		/// <param name="arParamNames">Un array cu numele parametrilor (new string[]{"Nume1", "Nume2"}.</param>
		/// <param name="arParams">Un array cu valorile parametrilor (new object[]{"Valoare1", "Valoare2"}.</param>
		/// <returns>DataSet</returns>
		public static DataSet GetData(string strProcName, string[] arParamNames, object[] arParams)
		{
			return GetData(strProcName, arParamNames, arParams, true);
		}

        public static DataSet GetDataVariable(string strProcName, string[] arParamNames, object[] arParams)
        {
            return GetDataVar(strProcName, arParamNames, arParams, true);
        }

		/// <summary>
		/// Executa o procedura cu parametri.
		/// </summary>
		/// <param name="strProcName">Numele procedurii.</param>
		/// <param name="arParamNames">Un array cu numele parametrilor (new string[]{"Nume1", "Nume2"}.</param>
		/// <param name="arParams">Un array cu valorile parametrilor (new object[]{"Valoare1", "Valoare2"}.</param>
		/// <param name="bAddSchema">Adauga schema.</param>
		/// <returns>DataSet</returns>
		public static DataSet GetData(string strProcName, string[] arParamNames, object[] arParams, bool bAddSchema)
		{
			SqlConnection connection = null;
			DataSet ds = new DataSet();
			try
			{
                connection = new SqlConnection(GetConnectionString());
				connection.Open();

				SqlCommand cmd = connection.CreateCommand();
				cmd.CommandType = CommandType.StoredProcedure;
				cmd.CommandText = strProcName;
				for (int i = 0; i < arParamNames.Length; ++i)
					cmd.Parameters.AddWithValue(arParamNames[i], arParams[i]);
				SqlDataAdapter adapter = new SqlDataAdapter(cmd);
				adapter.SelectCommand.CommandTimeout = 600;

				if (bAddSchema)
					adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
				adapter.Fill(ds);
			}
			finally
			{
				if (connection != null)
					connection.Close();
			}

			return ds;
		}

        private static DataSet GetDataVar(string strProcName, string[] arParamNames, object[] arParams, bool bAddSchema)
        {
            SqlConnection connection = null;
            DataSet ds = new DataSet();
            try
            {
                connection = new SqlConnection(variableConnectionString);
                connection.Open();

                SqlCommand cmd = connection.CreateCommand();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = strProcName;
                for (int i = 0; i < arParamNames.Length; ++i)
                    cmd.Parameters.AddWithValue(arParamNames[i], arParams[i]);
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                adapter.SelectCommand.CommandTimeout = 600;

                if (bAddSchema)
                    adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
                adapter.Fill(ds);
            }
            finally
            {
                if (connection != null)
                    connection.Close();
            }

            return ds;
        }

		/// <summary>
		/// Executa o procedura cu parametri.
		/// </summary>
		/// <param name="strProcName">Numele procedurii.</param>
		/// <param name="arParams">Un array cu valorile parametrilor (new object[]{"Valoare1", "Valoare2"}.</param>
		/// <returns>DataSet</returns>
		public static DataSet GetData(string strProcName, object[] arParams)
		{
			SqlConnection connection = null;
			DataSet ds = new DataSet();
			try
			{
				connection = new SqlConnection(GetConnectionString());
				connection.Open();

				SqlCommand cmd = connection.CreateCommand();
				cmd.CommandType = CommandType.StoredProcedure;
				cmd.CommandText = strProcName;
				SqlCommandBuilder.DeriveParameters(cmd);
				if (cmd.Parameters.Count > 0 && cmd.Parameters[0].ParameterName == "@RETURN_VALUE")
					cmd.Parameters.RemoveAt("@RETURN_VALUE");
				for (int i = 0; i < arParams.Length; ++i)
					cmd.Parameters[i].Value = arParams[i];
				SqlDataAdapter adapter = new SqlDataAdapter(cmd);
				adapter.SelectCommand.CommandTimeout = 600;

				adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
				adapter.Fill(ds);
			}
			finally
			{
				if (connection != null)
					connection.Close();
			}

			return ds;
		}

		/// <summary>
		/// Executa o comanda SQL.
		/// </summary>
		/// <param name="strSQL">Comanda SQL</param>
		/// <returns>DataSet</returns>
		public static DataSet GetData(string strSQL)
		{
			SqlConnection connection = null;
			DataSet ds = new DataSet();
			try
			{
				connection = new SqlConnection(GetConnectionString());
				SqlDataAdapter adapter = new SqlDataAdapter(strSQL, connection);
				adapter.SelectCommand.CommandTimeout = 600;

				adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
				adapter.Fill(ds);
			}
			catch (Exception ex)
			{
				throw new ApplicationException(ex.Message, new ApplicationException(strSQL, ex));
			}
			finally
			{
				if (connection != null)
					connection.Close();
			}

			return ds;
		}

        public static DataSet GetData(string strSQL, bool missingSchema)
        {
            SqlConnection connection = null;
            DataSet ds = new DataSet();
            try
            {
                connection = new SqlConnection(GetConnectionString());
                SqlDataAdapter adapter = new SqlDataAdapter(strSQL, connection);
                adapter.SelectCommand.CommandTimeout = 600;

                if(missingSchema)
                    adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
                adapter.Fill(ds);
            }
            catch (Exception ex)
            {
                throw new ApplicationException(ex.Message, new ApplicationException(strSQL, ex));
            }
            finally
            {
                if (connection != null)
                    connection.Close();
            }

            return ds;
        }

        public static DataSet GetDataVar(string strSQL)
        {
            SqlConnection connection = null;
            DataSet ds = new DataSet();
            try
            {
                connection = new SqlConnection(variableConnectionString);
                SqlDataAdapter adapter = new SqlDataAdapter(strSQL, connection);
                adapter.SelectCommand.CommandTimeout = 600;

                adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
                adapter.Fill(ds);
            }
            catch (Exception ex)
            {
                throw new ApplicationException(ex.Message, new ApplicationException(strSQL, ex));
            }
            finally
            {
                if (connection != null)
                    connection.Close();
            }

            return ds;
        }

		public static int ExecuteAll(object[] arCommands, bool bUseTran)
		{
			SqlConnection connection = null;
			SqlTransaction objTran = null;
			int res = 0;
			try
			{
				connection = new SqlConnection(GetConnectionString());
				connection.Open();
				if (bUseTran)
					objTran = connection.BeginTransaction();

				for (int k = 0; k < arCommands.Length; ++k )
				{
					string strProcName = (string)((object[])arCommands[k])[0];
					string[] arParamNames = (string[])((object[])arCommands[k])[1];
					object[] arParams = (object[])((object[])arCommands[k])[2];

					SqlCommand cmd = connection.CreateCommand();
					cmd.CommandType = CommandType.StoredProcedure;
					cmd.CommandText = strProcName;
					if (bUseTran)
						cmd.Transaction = objTran;
					for (int i = 0; i < arParamNames.Length; ++i)
						cmd.Parameters.AddWithValue(arParamNames[i], arParams[i]);

					res += cmd.ExecuteNonQuery();
				}
				if (bUseTran)
					objTran.Commit();
			}
			catch (Exception)
			{
				if (bUseTran)
				{
					try
					{
						objTran.Rollback();
					}
					catch (Exception) { }
				}

				throw;
			}
			finally
			{
				if (connection != null)
					connection.Close();
			}
			return res;
		}

		/// <summary>
		/// Executa o comanda SQL care nu intoarce un rezultat.
		/// </summary>
		/// <param name="strProcName">Numele procedurii.</param>
		/// <param name="arParamNames">Un array cu numele parametrilor (new string[]{"Nume1", "Nume2"}.</param>
		/// <param name="arParams">Un array cu valorile parametrilor (new object[]{"Valoare1", "Valoare2"}.</param>
		/// <returns>Numarul de inregistrari afectate.</returns>
		public static int Execute(string strProcName, string[] arParamNames, object[] arParams)
		{
			SqlConnection connection = new SqlConnection(GetConnectionString());
			try
			{
				connection.Open();

				SqlCommand cmd = connection.CreateCommand();
                //cmd.CommandTimeout = 6000;
				cmd.CommandType = CommandType.StoredProcedure;
				cmd.CommandText = strProcName;
				for (int i = 0; i < arParamNames.Length; ++i)
					cmd.Parameters.AddWithValue(arParamNames[i], arParams[i]);

				return cmd.ExecuteNonQuery();
			}
			finally
			{
				if (connection != null)
					connection.Close();
			}
		}

		/// <summary>
		/// Executa o comanda SQL care nu intoarce un rezultat.
		/// </summary>
		/// <param name="strProcName">Numele procedurii.</param>
		/// <param name="arParams">Un array cu valorile parametrilor (new object[]{"Valoare1", "Valoare2"}.</param>
		/// <returns>Numarul de inregistrari afectate.</returns>
		public static int Execute(string strProcName, object[] arParams)
		{
			SqlConnection connection = new SqlConnection(GetConnectionString());
			try
			{
				connection.Open();

				SqlCommand cmd = connection.CreateCommand();
				cmd.CommandType = CommandType.StoredProcedure;
				cmd.CommandText = strProcName;
				SqlCommandBuilder.DeriveParameters(cmd);
				if (cmd.Parameters.Count > 0 && cmd.Parameters[0].ParameterName == "@RETURN_VALUE")
					cmd.Parameters.RemoveAt("@RETURN_VALUE");
				for (int i = 0; i < arParams.Length; ++i)
					cmd.Parameters[i].Value = arParams[i];

				return cmd.ExecuteNonQuery();
			}
			finally
			{
				if (connection != null)
					connection.Close();
			}
		}

        public static int ExecuteVariable(string strProcName, object[] arParams)
        {
            SqlConnection connection = new SqlConnection(variableConnectionString);
            try
            {
                connection.Open();

                SqlCommand cmd = connection.CreateCommand();
                cmd.CommandTimeout = 6000;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = strProcName;
                SqlCommandBuilder.DeriveParameters(cmd);
                if (cmd.Parameters.Count > 0 && cmd.Parameters[0].ParameterName == "@RETURN_VALUE")
                    cmd.Parameters.RemoveAt("@RETURN_VALUE");
                for (int i = 0; i < arParams.Length; ++i)
                    cmd.Parameters[i].Value = arParams[i];

                return cmd.ExecuteNonQuery();
            }
            finally
            {
                if (connection != null)
                    connection.Close();
            }
        }

        public static int ExecuteVariable(string strSQL)
        {
            SqlConnection connection = new SqlConnection(variableConnectionString);
            try
            {
                connection.Open();

                SqlCommand cmd = connection.CreateCommand();
                cmd.CommandText = strSQL;
                return cmd.ExecuteNonQuery();
            }
			catch (Exception ex)
			{
				throw new ApplicationException(ex.Message, new ApplicationException(strSQL, ex));
			}
			finally
            {
                if (connection != null)
                    connection.Close();
            }
        }

		/// <summary>
		/// Executa o comanda SQL care nu intoarce un rezultat.
		/// </summary>
		/// <param name="strSQL">Comanda SQL</param>
		/// <returns>Numarul de inregistrari afectate.</returns>
		public static int Execute(string strSQL)
		{
			SqlConnection connection = new SqlConnection(GetConnectionString());
			try
			{
				connection.Open();

				SqlCommand cmd = connection.CreateCommand();
				cmd.CommandText = strSQL;
				return cmd.ExecuteNonQuery();
			}
			catch (Exception ex)
			{
				throw new ApplicationException(ex.Message, new ApplicationException(strSQL, ex));
			}
			finally
			{
				if (connection != null)
					connection.Close();
			}
		}

		public static int SaveAll(DataSet ds, object[] objInsertParams, object[] objUpdateParams, object[] objDeleteParams)
		{
			return SaveAll(ds, objInsertParams, objUpdateParams, objDeleteParams, -1);
		}

		public static int SaveAll(DataSet ds, object[] objInsertParams, object[] objUpdateParams, object[] objDeleteParams, int nTableIndex)
		{
			// new object[] { objParamsForTable0, objParamsForTable1 }
			SqlConnection connection = null;
			SqlTransaction objTran = null;
			int res = 0;
			try
			{
				connection = new SqlConnection(GetConnectionString());
				connection.Open();
				objTran = connection.BeginTransaction();

				SqlDataAdapter m_objAdapt = BuildAdapter(connection, objTran);

				int nTable = 0;
				ArrayList arrTables = new ArrayList();
				//liniile sterse
				for (int k = ds.Relations.Count - 1; k >= 0; --k)
					if (!arrTables.Contains(ds.Tables.IndexOf(ds.Relations[k].ChildTable.TableName)) && ds.Relations[k].ChildTable.GetChanges(DataRowState.Deleted) != null)
					{
						nTable = ds.Tables.IndexOf(ds.Relations[k].ChildTable.TableName);
						if (nTableIndex != -1 && nTable != nTableIndex)
							continue;
						arrTables.Add(nTable);
						SetParameters(m_objAdapt, nTable, objInsertParams, objUpdateParams, objDeleteParams);

						try
						{
							m_objAdapt.Update(ds.Relations[k].ChildTable.Select("", "", DataViewRowState.Deleted));
						}
						catch (DBConcurrencyException) { }
					}

				for (int k = 0; k < ds.Tables.Count; ++k)
					if (!arrTables.Contains(k) && ds.Tables[k].GetChanges(DataRowState.Deleted) != null)
					{
						if (nTableIndex != -1 && k != nTableIndex)
							continue;
						arrTables.Add(k);
						SetParameters(m_objAdapt, k, objInsertParams, objUpdateParams, objDeleteParams);

						try
						{
							m_objAdapt.Update(ds.Tables[k].Select("", "", DataViewRowState.Deleted));
						}
						catch (DBConcurrencyException) { }
					}
				//-------------------------
				arrTables = new ArrayList();
				//liniile modificate si liniile adaugate
				for (int k = 0; k < ds.Relations.Count; ++k)
					if (!arrTables.Contains(ds.Tables.IndexOf(ds.Relations[k].ParentTable.TableName)) && ds.Relations[k].ParentTable.GetChanges(DataRowState.Added | DataRowState.Modified) != null)
					{
						nTable = ds.Tables.IndexOf(ds.Relations[k].ParentTable.TableName);
						if (nTableIndex != -1 && nTable != nTableIndex)
							continue;
						arrTables.Add(nTable);
						SetParameters(m_objAdapt, nTable, objInsertParams, objUpdateParams, objDeleteParams);

						try
						{
							m_objAdapt.Update(ds.Relations[k].ParentTable.Select("", "", DataViewRowState.ModifiedCurrent));
						}
						catch (DBConcurrencyException) { }

						try
						{
							m_objAdapt.Update(ds.Relations[k].ParentTable.Select("", "", DataViewRowState.Added));
						}
						catch (DBConcurrencyException) { }
					}

				for (int k = 0; k < ds.Tables.Count; ++k)
					if (!arrTables.Contains(k) && ds.Tables[k].GetChanges(DataRowState.Added | DataRowState.Modified) != null)
					{
						arrTables.Add(k);
						if (nTableIndex != -1 && k != nTableIndex)
							continue;
						SetParameters(m_objAdapt, k, objInsertParams, objUpdateParams, objDeleteParams);

						try
						{
							m_objAdapt.Update(ds.Tables[k].Select("", "", DataViewRowState.ModifiedCurrent));
						}
						catch (DBConcurrencyException) { }

						try
						{
							m_objAdapt.Update(ds.Tables[k].Select("", "", DataViewRowState.Added));
						}
						catch (DBConcurrencyException) { }
					}
				//-------------------------

				objTran.Commit();
				if (nTableIndex == -1)
					ds.AcceptChanges();
				else
					ds.Tables[nTableIndex].AcceptChanges();
			}
			catch (Exception)
			{
				try
				{
					objTran.Rollback();
				}
				catch (Exception) {}

				throw;
			}
			finally
			{
				if (connection != null)
					connection.Close();
			}
			return res; // == return 0;
		}

		private static SqlDataAdapter BuildAdapter(SqlConnection m_objConn, SqlTransaction objTran)
		{
			SqlDataAdapter m_objAdapt = new SqlDataAdapter();
			m_objAdapt.MissingSchemaAction = MissingSchemaAction.AddWithKey;
			m_objAdapt.MissingMappingAction = MissingMappingAction.Passthrough;
/*
			m_objAdapt.SelectCommand = new SqlCommand();
			m_objAdapt.SelectCommand.CommandType = CommandType.StoredProcedure;
			m_objAdapt.SelectCommand.Connection = m_objConn;
			m_objAdapt.SelectCommand.CommandTimeout = 600;
			m_objAdapt.SelectCommand.Transaction = objTran;
*/
			m_objAdapt.InsertCommand = new SqlCommand();
			m_objAdapt.InsertCommand.CommandType = CommandType.StoredProcedure;
			m_objAdapt.InsertCommand.Connection = m_objConn;
			m_objAdapt.InsertCommand.CommandTimeout = 600;
			m_objAdapt.InsertCommand.Transaction = objTran;

			m_objAdapt.UpdateCommand = new SqlCommand();
			m_objAdapt.UpdateCommand.CommandType = CommandType.StoredProcedure;
			m_objAdapt.UpdateCommand.Connection = m_objConn;
			m_objAdapt.UpdateCommand.CommandTimeout = 600;
			m_objAdapt.UpdateCommand.Transaction = objTran;

			m_objAdapt.DeleteCommand = new SqlCommand();
			m_objAdapt.DeleteCommand.CommandType = CommandType.StoredProcedure;
			m_objAdapt.DeleteCommand.Connection = m_objConn;
			m_objAdapt.DeleteCommand.CommandTimeout = 600;
			m_objAdapt.DeleteCommand.Transaction = objTran;

			m_objAdapt.InsertCommand.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord;
			m_objAdapt.RowUpdated += new SqlRowUpdatedEventHandler(OnGeneralUpdated);

			return m_objAdapt;
		}

		private static void SetParameters(SqlDataAdapter m_objAdapt, int nTable, object[] objInsertParamsAll, object[] objUpdateParamsAll, object[] objDeleteParamsAll)
		{
			m_objAdapt.InsertCommand.Parameters.Clear();
			m_objAdapt.UpdateCommand.Parameters.Clear();
			m_objAdapt.DeleteCommand.Parameters.Clear();

			if (objInsertParamsAll != null && objInsertParamsAll.Length > nTable && objInsertParamsAll[nTable] != null)
			{
				object[] objInsertParams = (object[])objInsertParamsAll[nTable];
				m_objAdapt.InsertCommand.CommandType = CommandType.StoredProcedure;
				m_objAdapt.InsertCommand.CommandText = (string)objInsertParams[0];
				m_objAdapt.InsertCommand.Parameters.AddRange(GetParamsArray(objInsertParams));
			}
			else
			{
				m_objAdapt.InsertCommand.CommandType = CommandType.Text;
				m_objAdapt.InsertCommand.CommandText = "/*INSERT TABLE " + nTable.ToString() + "*/";
			}

			if (objUpdateParamsAll != null && objUpdateParamsAll.Length > nTable && objUpdateParamsAll[nTable] != null)
			{
				object[] objUpdateParams = (object[])objUpdateParamsAll[nTable];
				m_objAdapt.UpdateCommand.CommandType = CommandType.StoredProcedure;
				m_objAdapt.UpdateCommand.CommandText = (string)objUpdateParams[0];
				m_objAdapt.UpdateCommand.Parameters.AddRange(GetParamsArray(objUpdateParams));
			}
			else
			{
				m_objAdapt.UpdateCommand.CommandType = CommandType.Text;
				m_objAdapt.UpdateCommand.CommandText = "/*UPDATE TABLE " + nTable.ToString() + "*/";
			}

			if (objDeleteParamsAll != null && objDeleteParamsAll.Length > nTable && objDeleteParamsAll[nTable] != null)
			{
				object[] objDeleteParams = (object[])objDeleteParamsAll[nTable];
				m_objAdapt.DeleteCommand.CommandType = CommandType.StoredProcedure;
				m_objAdapt.DeleteCommand.CommandText = (string)objDeleteParams[0];
				m_objAdapt.DeleteCommand.Parameters.AddRange(GetParamsArray(objDeleteParams));
			}
			else
			{
				m_objAdapt.DeleteCommand.CommandType = CommandType.Text;
				m_objAdapt.DeleteCommand.CommandText = "/*DELETE TABLE " + nTable.ToString() + "*/";
			}
		}

		private static void OnGeneralUpdated(object sender, SqlRowUpdatedEventArgs args)
		{
			if (args.Row.RowState == DataRowState.Deleted)
				return;

			for (int i = 0; i < args.Row.Table.DataSet.Relations.Count; ++i)
			{
				if (args.Row.Table == args.Row.Table.DataSet.Relations[i].ParentTable)
				{
					for (int j = 0; j < args.Row.Table.DataSet.Relations[i].ParentColumns.Length; ++j)
					{
						object currentkey = args.Row[args.Row.Table.DataSet.Relations[i].ParentColumns[j].ColumnName];
						DataRow[] dr;
						try
						{
							dr = args.Row.GetChildRows(args.Row.Table.DataSet.Relations[i].RelationName, DataRowVersion.Original);
						}
						catch (VersionNotFoundException)
						{
							dr = args.Row.GetChildRows(args.Row.Table.DataSet.Relations[i].RelationName);
						}
						for (int k = 0; k < dr.Length; ++k)
//							if (!m_bCheckUnmodifed || dr[k].RowState != DataRowState.Unchanged)
							if (dr[k].RowState != DataRowState.Unchanged)
								dr[k][args.Row.Table.DataSet.Relations[i].ChildColumns[j].ColumnName] = currentkey;
					}
				}
			}
		}

		/// <summary>
		/// Returneaza parametrii unei proceduri.
		/// </summary>
		/// <param name="objParams">new object[] { "ProcName", new string[] { "Param1", "Param2" }, new object[] { new object[] {"Param11", objParam11_Value } } }</param>
		/// <returns>Parametrii.</returns>
		private static SqlParameter[] GetParamsArray(object[] objParams)
		{
			string[] arParams = (string[])objParams[1];
			int l = arParams.Length;
			if (objParams.Length >= 3)
			{// Add params with default values
				object[] arParamsDefaultValues = (object[])objParams[2];
				l += arParamsDefaultValues.Length;
			}

			SqlParameter[] pa = new SqlParameter[l];
			for (int i = 0; i < arParams.Length; ++i)
			{
				pa[i] = new SqlParameter();
				pa[i].ParameterName = arParams[i];
				pa[i].SourceColumn = arParams[i];
			}

			if (objParams.Length >= 3)
			{// Add params with default values
				object[] arParamsDefaultValues = (object[])objParams[2];
				for (int i = 0; i < arParamsDefaultValues.Length; ++i)
				{
					object[] objParamsDefaultValues = (object[])arParamsDefaultValues[i];
					pa[arParams.Length + i] = new SqlParameter(objParamsDefaultValues[0].ToString(), objParamsDefaultValues[1]);
				}
			}

			return pa;
		}
/*
		/// <summary>
		/// Returneaza parametrii unei proceduri.
		/// </summary>
		/// <param name="objParams">new object[] { "ProcName", new string[] { "Param1", "Param2" } }</param>
		/// <returns>Parametrii.</returns>
		private static SqlParameter[] GetParamsArray(object[] objParams)
		{
			string strProcName = (string)objParams[0];
			string[] arParams = (string[])objParams[1];

			ArrayList al = new ArrayList();
			al.AddRange(arParams);

			SqlParameterCollection pc = GetParams(strProcName);
			for (int i = pc.Count - 1; i >= 0; --i)
			{
				string s = pc[i].ParameterName;
				if (s.StartsWith("@"))
					s = s.Substring(1);
				if (!al.Contains(s))
					pc.RemoveAt(i);
				else
					pc[i].SourceColumn = s;
			}

			SqlParameter[] pa = new SqlParameter[pc.Count];
			pc.CopyTo(pa, 0);
			pc.Clear();

			return pa;
		}
*/
		/// <summary>
		/// Returneaza parametrii unei proceduri.
		/// </summary>
		/// <param name="strProcName">Numele procedurii.</param>
		/// <returns>Parametrii.</returns>
		public static SqlParameterCollection GetParams(string strProcName)
		{
			SqlConnection connection = new SqlConnection(GetConnectionString());
			try
			{
				connection.Open();

				SqlCommand cmd = connection.CreateCommand();
				cmd.CommandType = CommandType.StoredProcedure;
				cmd.CommandText = strProcName;
				SqlCommandBuilder.DeriveParameters(cmd);
				if (cmd.Parameters.Count > 0 && cmd.Parameters[0].ParameterName == "@RETURN_VALUE")
					cmd.Parameters.RemoveAt("@RETURN_VALUE");
				return cmd.Parameters;
			}
			finally
			{
				if (connection != null)
					connection.Close();
			}
		}

        /// <summary>
        /// Executa o comanda OleDb care intoarce un DataSet.
        /// </summary>
        /// <param name="query">Query</param>
        /// /// <param name="pathFile">File Path</param>
        /// <returns>Un DataSet.</returns>
        public static DataSet GetDataSet_Access(string query, string pathFile)
        {
			if (!System.IO.File.Exists(pathFile))
				throw new ApplicationException("Cannot find file: " + pathFile);
			OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + pathFile + ";");
            try
            {
				conn.Open();
                OleDbDataAdapter da = new OleDbDataAdapter(query, conn);
				DataSet rez = new DataSet();
				da.Fill(rez);
				return rez;
			}
			catch (OleDbException ex)
			{
				throw new ApplicationException(ex.Message + string.Format(" (HRESULT = {0} / 0x{0:X})", ex.ErrorCode), new ApplicationException(query, ex));
			}
			finally
            {
                conn.Close();
            }
        }

        /// <summary>
        /// Executa o comanda OleDb care intoarce un DataSet.
        /// </summary>
        /// <param name="query">Query</param>
        /// /// <param name="pathFile">File Path</param>
        /// <returns>Un DataSet.</returns>
		public static DataSet GetDataSet_Access(string query, OleDbConnection conn)
		{
			if (conn.State != ConnectionState.Open)
				throw new ApplicationException("Supplied connection was not open!");
			try
			{
				OleDbDataAdapter da = new OleDbDataAdapter(query, conn);
				DataSet rez = new DataSet();
				da.Fill(rez);
				return rez;
			}
			catch (OleDbException ex)
			{
				throw new ApplicationException(ex.Message + string.Format(" (HRESULT = {0} / 0x{0:X})", ex.ErrorCode), new ApplicationException(query, ex));
			}
		}

        /// <summary>
        /// Executa o comanda OleDb care intoarce un DataSet.
        /// </summary>
        /// <param name="query">Query</param>
        /// /// <param name="pathFile">File Path</param>
        /// <returns>Un DataSet.</returns>
        public static object ExecuteScalarQuery_Access(string query, string pathFile)
        {
			if (!System.IO.File.Exists(pathFile))
				throw new ApplicationException("Cannot find file: " + pathFile);
			OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + pathFile + ";");
            try
            {
                conn.Open();
                OleDbCommand cmd = new OleDbCommand(query, conn);
                return cmd.ExecuteNonQuery();
            }
			catch (OleDbException ex)
			{
				throw new ApplicationException(ex.Message + string.Format(" (HRESULT = {0} / 0x{0:X})", ex.ErrorCode), new ApplicationException(query, ex));
			}
			finally
            {
                conn.Close();
            }
        }

        /// <summary>
        /// Executa o comanda OleDb care intoarce un DataSet.
        /// </summary>
        /// <param name="query">Query</param>
        /// /// <param name="pathFile">File Path</param>
        /// <returns>Un DataSet.</returns>
		public static object ExecuteScalarQuery_Access(string query, OleDbConnection conn)
		{
			if (conn.State != ConnectionState.Open)
				throw new ApplicationException("Supplied connection was not open!");
			try
			{
				OleDbCommand cmd = new OleDbCommand(query, conn);
				return cmd.ExecuteNonQuery();
			}
			catch (OleDbException ex)
			{
				throw new ApplicationException(ex.Message + string.Format(" (HRESULT = {0} / 0x{0:X})", ex.ErrorCode), new ApplicationException(query, ex));
			}
		}

        /// <summary>
        /// Executa o comanda Odbc care intoarce un DataSet.
        /// </summary>
        /// <param name="query">Query</param>
        /// /// <param name="pathFile">File Path</param>
        /// <returns>Un DataSet.</returns>
        public static DataTable GetDataTable_DBF(string query, string pathFile, string nameFile)
        {
            try
            {
                OdbcConnection oConn = new OdbcConnection();
                oConn.ConnectionString = @"Driver={Microsoft dBase Driver (*.dbf)};SourceType=DBF;SourceDB=" + pathFile + ";Exclusive=No; Collate=Machine;NULL=NO;DELETED=NO;BACKGROUNDFETCH=NO;";
                oConn.Open();
                OdbcCommand oCmd = oConn.CreateCommand();
                oCmd.CommandText = query + " " + pathFile + nameFile;
                DataTable dt = new DataTable();
                dt.Load(oCmd.ExecuteReader());
                oConn.Close();

                return dt; 
            }
            catch (OdbcException ex)
            {
                throw new ApplicationException(ex.Message + string.Format(" (HRESULT = {0} / 0x{0:X})", ex.ErrorCode), new ApplicationException(query, ex));
            }
        }

        /// <summary>
        /// Executa o comanda Odbc care intoarce un DataSet.
        /// </summary>
        /// <param name="query">Query</param>
        /// /// <param name="pathFile">File Path</param>
        /// <returns>Un DataSet.</returns>
        public static object ExecuteNonQuery_DBF(string query, string pathFile, OleDbConnection oConn)
        {
            OleDbCommand command;
            if (oConn.State != ConnectionState.Open)
                throw new ApplicationException("Supplied connection was not open!");
            try
            {
                command = oConn.CreateCommand();
                command.CommandText = query;
                return command.ExecuteNonQuery();
            }
            catch (OleDbException ex)
            {
                throw new ApplicationException(ex.Message + string.Format(" (HRESULT = {0} / 0x{0:X})", ex.ErrorCode), new ApplicationException(query, ex));
            }
        }
	}
}
